import os
import traceback
import uuid
from time import sleep

import xlsxwriter
from flask import render_template, request, jsonify, current_app
from flask_appbuilder.models.sqla.interface import SQLAInterface
from flask_appbuilder import ModelView, ModelRestApi, expose

from . import appbuilder, db
from .common.function import read_excel, listdirs, du_php, export_file
import datetime

"""
    Create your Model based REST API::

    class MyModelApi(ModelRestApi):
        datamodel = SQLAInterface(MyModel)

    appbuilder.add_api(MyModelApi)


    Create your Views::


    class MyModelView(ModelView):
        datamodel = SQLAInterface(MyModel)


    Next, register your Views::


    appbuilder.add_view(
        MyModelView,
        "My View",
        icon="fa-folder-open-o",
        category="My Category",
        category_icon='fa-envelope'
    )
"""

"""
    Application wide 404 error handler
"""


@appbuilder.app.errorhandler(404)
def page_not_found(e):
    return (
        render_template(
            "404.html", base_template=appbuilder.base_template, appbuilder=appbuilder
        ),
        404,
    )

@appbuilder.app.route('/do', methods=['GET'])
def do():
    # upload_path = "G:\\python\\excel2language\\wenjian\\语言包整理20201104.xlsx"
    # upload_path = "E:\\python\\excel2language\\wenjian\\CCDS-WEB_languages--20201116.xlsx"
    upload_path = "G:\\python\\excel2language\\wenjian\\app_languages-20201123231539.xlsx"
    res = read_excel(upload_path)
    print(res)
    #  写入文件
    for r in res:
        UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
        filename = "app_languages.php"
        save_path = os.path.join(UPLOAD_FOLDER, r['name'])
        filePath = os.path.join(save_path, filename)
        print(filePath)
        if not os.path.exists(save_path):
            os.makedirs(save_path)
        encoding = "utf-8"
        with open(filePath, "w+", encoding=encoding) as f:
            s1=[]
            s1.append("<?php")
            addtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            s1.append("//{} {}".format("puamac <3162289690@qq.com>", addtime))
            s1.append("$text=array();")
            for m in r['main']:
                for i in range(len(r['header'])):
                    if i>0:
                        try:
                            s1.append("$text['{}']['{}']=\"{}\";".format(m[0], r['header'][i].lower(), m[r['header'][i]]))
                        except Exception as e:
                            print( 'Error', e )
                            print( 'Error', m )
                            print( 'traceback.print_exc():', traceback.print_exc() )
                            print( 'traceback.format_exc():', traceback.format_exc() )
            s1.append("?>")
            f.write("\n".join(s1))
            sleep(1)
            # 关闭打开的文件
            f.close()
    result = {
        "code": 1,
        "message": 'success'
    }
    return jsonify(result)


# --
# -- 表的结构 `v_menu_languages`
# --
#
# CREATE TABLE IF NOT EXISTS `v_menu_languages` (
#   `menu_language_uuid` char(36) DEFAULT NULL,
#   `menu_uuid` char(36) DEFAULT NULL,
#   `menu_item_uuid` char(36) DEFAULT NULL,
#   `menu_language` text,
#   `menu_item_title` text
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# INSERT INTO `v_menu_languages` (`menu_language_uuid`, `menu_uuid`, `menu_item_uuid`, `menu_language`, `menu_item_title`) VALUES
# ('6d528996-4f3c-4511-b687-a11a1e9efffc', '049c7392-3845-4fff-aaa5-152f0cc4dd36', '1f59d07b-b4f7-4f9e-bde9-312cf491d66e', 'en-us', 'Adminer')
@appbuilder.app.route('/domenu', methods=['GET'])
def domenu():
    # upload_path = "C:\\Users\\Administrator\\Desktop\\华券\\语言包\\10月29客户翻译回来\\menu-0905.xlsx"
    upload_path = "G:\\python\\excel2language\\wenjian\\menu_languages-103.xlsx"
    res = read_excel(upload_path)
    print(res)
    #  写入文件
    r=res[0]
    UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
    addtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    filename = "menu_languages-{}.sql".format(addtime)
    save_path = os.path.join(UPLOAD_FOLDER, r['name'])
    filePath = os.path.join(save_path, filename)
    print(filePath)
    if not os.path.exists(save_path):
        os.makedirs(save_path)

    d = menu_items_key()
    menu_item_uuid = '1f59d07b-b4f7-4f9e-bde9-312cf491d66e'
    encoding = "utf-8"
    with open(filePath, "w+", encoding=encoding) as f:
        s1 = []
        for m in r['main']:
            if m['zh-cn'] in d.keys():
                menu_item_uuid = d[m['zh-cn']]
            else:
                menu_item_uuid = uuid.uuid4()
            for i in range(len(r['header'])):
                menu_language_uuid = uuid.uuid4()
                str ="INSERT INTO `v_menu_languages` (`menu_language_uuid`, `menu_uuid`, `menu_item_uuid`, `menu_language`, `menu_item_title`) VALUES('{}','{}','{}','{}','{}');".format(menu_language_uuid,'b4750c3f-2a86-b00d-b7d0-345c14eca286', menu_item_uuid, r['header'][i], m[r['header'][i]])
                s1.append(str)

        f.write("\n".join(s1))
        # 关闭打开的文件
        f.close()
    result = {
        "code": 1,
        "message": 'success'
    }
    return jsonify(result)

@appbuilder.app.route('/domenu1', methods=['GET'])
def domenu1():
    # upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_languages-791.csv"
    upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_languages-1732.csv"
    encoding = "utf-8"
    import csv
    encoding = "utf-8"
    with open(upload_path, 'r', encoding=encoding) as f:
        reader = csv.reader(f)
        data = {}
        i=0
        for row in reader:
            print(row)
            # row[2] = row[2] if row[2] != '' else i
            if row[2] not in data.keys():
                data[row[2]] = {}
            data[row[2]][row[3]] = row[4]
            i = i+1
    print(data)
    #  写入文件
    # r=res[0]
    UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
    addtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    filename = "menu_languages-{}.xlsx".format(addtime)
    filePath = os.path.join(UPLOAD_FOLDER, filename)
    print(filePath)
    export_file(data, 'menu_languages', filePath)
    # if not os.path.exists(save_path):
    #     os.makedirs(save_path)
    result = {
        "code": 1,
        "message": 'success'
    }
    return jsonify( result )


@appbuilder.app.route('/zhengli', methods=['GET'])
def zhengli():
    upload_path = "E:\\www\\test\\huamai\\huamaiweb\\dispachserver\\web_yu"

    UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
    isfilename = "app_languages.php"
    addtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    filename = "app_languages-{}.xlsx".format(addtime)
    output = os.path.join(UPLOAD_FOLDER, filename)
    print(output)
    list_dirs = listdirs(path = upload_path, type = isfilename)
    print(list_dirs)
    for i in list_dirs:
        r = du_php( i )
        export_file( r, "-".join(i.split("\\")[-3:][0:2]), output )
        sleep(3)
    encoding = "utf-8"
    result = {
        "code": 1,
        "message": 'success zhengli'
    }
    return jsonify(result)

# 多合一
@appbuilder.app.route('/zhengli1', methods=['GET'])
def zhengli1():
    upload_path = "E:\\www\\test\\huamai\\huamaiweb\\dispachserver\\web_yu"

    UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
    isfilename = "app_languages.php"
    addtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    filename = "app_languages-{}.xlsx".format(addtime)
    output = os.path.join(UPLOAD_FOLDER, filename)
    print(output)
    list_dirs = listdirs(path = upload_path, type = isfilename)
    print(list_dirs)
    # 写excel
    workbook = xlsxwriter.Workbook(output)  # 先创建一个book，直接写到io中

    for d in list_dirs:
        r = du_php( d )
        # export_file( r, "-".join(d.split("\\")[-3:][0:2]), filePath )
        sheet_name = "-".join(d.split("\\")[-3:][0:2])
        header = [0] + [v2 for v2 in [v for k, v in r.items()][0].keys()]
        # 整理未翻译的
        if "spanish" in header:
            continue
        sheet = workbook.add_worksheet(sheet_name)
        keys_list = [k for k, v in r.items()]
        values_list = [v for k, v in r.items()]
        # 写入数据到A1一列
        sheet.write_row( 'A1', header )

        # 遍历有多少行数据
        for i in range(len(r)):
            # 遍历有多少列数据
            for x in range(len(header)):
                if header[x] == 0:
                    sheet.write(i + 1, x, keys_list[i])
                else:
                    if header[x] in values_list[i].keys():
                        sheet.write(i + 1, x, values_list[i][header[x]])
                    else:
                        sheet.write(i + 1, x, keys_list[i])
        sleep(3)

    workbook.close()  # 需要关闭
    encoding = "utf-8"
    result = {
        "code": 1,
        "message": 'success zhengli'
    }
    return jsonify(result)

@appbuilder.app.route('/domenu2', methods=['GET'])
def menu_items():
    # upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_languages-791.csv"
    upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_items2.csv"
    encoding = "utf-8"
    import csv
    encoding = "utf-8"
    with open(upload_path, 'r', encoding=encoding) as f:
        reader = csv.reader(f)
        data = {}
        i=0
        for row in reader:
            print(row)
            data[row[3]] = row[0]
            i = i+1
    print(data)
    result = {
        "code": 1,
        'data':data,
        "message": 'success zhengli'
    }
    return jsonify( result )

def menu_items_key():
    # upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_languages-791.csv"
    upload_path = "G:\\python\\excel2language\\wenjian\\v_menu_items2.csv"
    encoding = "utf-8"
    import csv
    encoding = "utf-8"
    with open(upload_path, 'r', encoding=encoding) as f:
        reader = csv.reader(f)
        data = {}
        i=0
        for row in reader:
            # print(row)
            data[row[3]] = row[0]
            i = i+1
    # print(data)
    return data


# 2021年3月23日 22:01:54  shop.puamac.cn 整理简单的卡密
@appbuilder.app.route('/findex', methods=['GET'])
def findex():
    UPLOAD_FOLDER = current_app.config.get('UPLOAD_FOLDER')
    addtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    filename = "shop_puamac-{}.xlsx".format(addtime)
    output = os.path.join(UPLOAD_FOLDER, 'shop', filename)
    print(output)
    # 写excel
    workbook = xlsxwriter.Workbook(output)  # 先创建一个book，直接写到io中
    sheet_name = "差价"
    header = ["差价"]
    value = None
    number = 10000
    sheet = workbook.add_worksheet(sheet_name)
    # 写入数据到A1一列
    sheet.write_row( 'A1', header )

    # 遍历有多少行数据
    for i in list(range(number)):
        # 遍历有多少列数据
        for x in range(len(header)):
            if value is None:
                write_str = str(uuid.uuid4())
                sheet.write(i + 1, x, write_str)
            else:
                sheet.write(i + 1, x, value)
    sleep(3)

    workbook.close()  # 需要关闭
    encoding = "utf-8"
    result = {
        "code": 1,
        "message": 'success zhengli'
    }
    return jsonify(result)

db.create_all()
